package com.zcgk.core.utils;

import com.zcgk.core.annotation.FieldName;
import com.zcgk.core.domain.CodeMsg;
import com.zcgk.core.exception.CosyException;
import com.zcgk.modules.file.entity.vo.resp.DownloadAttachmentRespVO;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.io.output.ByteArrayOutputStream;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

/****************************************************
 *
 * Excel工具类，含导入工具，导出工具。详见方法说明
 *
 *
 * @author Francis
 * @date 2019/12/31 13:13
 * @version 1.0
 **************************************************/
@Slf4j
public class ExcelUtils {


    /**
     * 导入Excel文件
     * 本方法暂不支持一次性导入 多sheet页 数据。
     * 若需导入多sheet页，请调用方多次调用。
     *
     * @param excelFile      上传的excel文件
     * @param sheetNum       导入excel的sheet页编号。如：导入该excel的sheet1页的数据，则该值为0。通常都为0。【sheet页起始值为0】
     * @param validColumnNum 有效的起始列。如：excel从A列开始即为字段数据，则该值为0。【列的起始值为0】
     * @param validRowNum    有效的起始行。如：excel从3行开始即为需导入数据，则该值为2。【行的起始值为0，一般第一行为中文标题，第二行为字段名】
     * @param fieldsNum      字段所在行。如：excel第2行为字段，则该值为1【行的起始值为0，一般第一行为中文标题，第二行为字段名】
     * @param clazz          T.class
     * @return 组装好的list 调用方执行入库即可
     */
    public static <T> List<T> importByExcel(MultipartFile excelFile, Integer sheetNum, Integer validColumnNum, Integer validRowNum, Integer fieldsNum, Class<T> clazz) {
        if (!(excelFile.getOriginalFilename().endsWith("xls") || excelFile.getOriginalFilename().endsWith("xlsx"))) {
            // 文件格式有误
            log.error("文件格式有误！");
            throw new CosyException(CodeMsg.FILE_IS_NOT_EXCEL);
        }

        HSSFWorkbook workbook = null;

        try {
            InputStream inputStream = excelFile.getInputStream();
            workbook = new HSSFWorkbook(inputStream);
        } catch (IOException e) {
            log.error("获取上传文件的输入流失败！请检查后端文件接收变量名与前端变量名是否一致！");
            throw new CosyException(CodeMsg.EXCEL_IS_ERROR);
        }

        HSSFSheet sheet = workbook.getSheetAt(sheetNum);

        Integer lastColumnNum = Integer.valueOf(sheet.getRow(0).getLastCellNum());

        // 获取数据类型字段集合
        List<String> fieldsStrList = getFieldsStrList(sheet.getRow(fieldsNum), validColumnNum, lastColumnNum);

        List<T> tList = new ArrayList<>();
        for (Row row : sheet) {
            T t = null;
            try {
                t = clazz.newInstance();
            } catch (InstantiationException e) {
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }

            // 过滤掉无效的行
            int rowNum = row.getRowNum();
            if (rowNum < validRowNum) {
                continue;
            }

            for (int colNum = validColumnNum; colNum < lastColumnNum; colNum++) {
                // 判断cell类型
                Object value = getValueThrowDifferentType(row.getCell(colNum));
                loadValue2Entity(t, clazz, value, fieldsStrList.get(colNum - validColumnNum));

            }
            tList.add(t);
        }
        return tList;

    }


    /**
     * 导出excel

     * @param objList 目标对象list
     * @param clazz obj.class
     * @param fileName 要导出的文件名, 不填则默认为 "自定义.xls"
     * @param sheetName 要导出的文件的工作簿名称, 不填则默认为"sheet1"
     * @param <T> 泛型
     * @return 文件数据
     */
    public static <T> DownloadAttachmentRespVO export2Excel(List<T> objList, Class<T> clazz, String fileName, String sheetName) {
        // 获取表头
        List<String> titleList = analysisAnnotation(clazz);

        // 创建工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet(StringUtils.isEmpty(sheetName) ? "sheet1" : sheetName);
        // 设置文件名称
        String exportFileName = StringUtils.isEmpty(fileName) ? "自定义" : fileName + ".xls";
        try {
            exportFileName = URLEncoder.encode(exportFileName, "UTF-8");
            exportFileName = new String(exportFileName.getBytes(), "iso-8859-1");
        } catch (UnsupportedEncodingException e) {
            log.error("格式化文件名称失败！", e);
            throw new CosyException(CodeMsg.EXCEL_NAME_ERROR);
        }


        // 设置表头样式
        HSSFCellStyle titleStyle = workbook.createCellStyle();

        HSSFFont font = workbook.createFont();
        font.setFontName("黑体");
        font.setBold(true);
        font.setFontHeightInPoints((short) 16);

        titleStyle.setFont(font);

        // 设置表头内容
        HSSFRow row = sheet.createRow(0);
        int index = 0;
        for (String title : titleList) {
            HSSFCell cell = row.createCell(index ++);

            cell.setCellValue(new HSSFRichTextString(title));
            cell.setCellStyle(titleStyle);
        }

        // 设置输出的值
        int rowValue = 1;
        try {
            for (T obj : objList) {
                index = 0;
                HSSFRow valueRow = sheet.createRow(rowValue ++);

                Class<?> tempClazz = obj.getClass();
                Field[] fieldArr = tempClazz.getDeclaredFields();
                for (Field field : fieldArr) {
                    field.setAccessible(true);
                    Object value = field.get(obj);

                    valueRow.createCell(index ++).setCellValue(String.valueOf(value));
                }
            }
        } catch (IllegalAccessException e) {
            log.error("导出excel：设置输出值失败！", e);
            throw new CosyException(CodeMsg.EXCEL_EXPORT_ERROR);
        }

        // 转为字节流
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        try {
            workbook.write(outputStream);
        } catch (IOException e) {
            log.error("转为字节流过程中出现异常！");
            throw new CosyException(CodeMsg.EXCEL_EXPORT_ERROR);
        }

        return new DownloadAttachmentRespVO(exportFileName, outputStream.toByteArray());
    }


    /**
     * 导入excel辅助：反射赋值
     *
     * @param t obj
     * @param clazz obj.class
     * @param value value
     * @param objFiled 对象字段
     * @param <T> 泛型
     */
    private static <T> void loadValue2Entity(T t, Class<T> clazz, Object value, String objFiled) {
        Field[] fields = clazz.getDeclaredFields();
        for (Field field : fields) {
            if (objFiled.equals(field.getName())) {
                try {
                    if (!Objects.isNull(value)) {
                        setValueThrowDifferentType(field, t, value);
                    }
                } catch (Exception e) {
                    log.error("反射赋值失败！当前字段：" + objFiled + ", 当前值：" + value , e);
                    throw new CosyException(CodeMsg.EXCEL_BIND_ENTITY_ERROR);
                }
            }
        }
    }


    /**
     * 给不同类型的cell框赋值
     *
     * @param field 字段
     * @param t obj
     * @param value value
     * @param <T> 泛型
     * @throws Exception ex
     */
    private static <T> void setValueThrowDifferentType(Field field, T t, Object value) throws Exception {
        String fieldType = field.getGenericType().toString();
        field.setAccessible(true);

        switch (fieldType) {
            case "class java.lang.Integer":
                field.set(t, Integer.parseInt(value.toString()));
                break;
            case "class java.lang.Double":
                field.set(t, Double.parseDouble(value.toString()));
                break;
            case "class java.lang.Boolean":
                Boolean flag = null;
                if ("是".equals(value.toString())) {
                    flag = true;
                } else if ("否".equals(value.toString())) {
                    flag = false;
                } else {
                    flag = Boolean.parseBoolean(value.toString());
                }
                field.set(t, flag);
                break;
            case "class java.lang.String":
                field.set(t, value.toString());
                break;
            case "class java.math.BigDecimal":
                field.set(t, new BigDecimal(value.toString()));
                break;
            default:
                log.error("待添加的转换类：" + fieldType);
                break;
        }
    }


    /**
     * 导入excel辅助： 根据 rowNum 获取字段List
     *
     * @param row            字段所在行
     * @param validColumnNum 有效的起始列
     * @param lastColumnNum  结束列
     * @return
     */
    private static List<String> getFieldsStrList(Row row, Integer validColumnNum, Integer lastColumnNum) {
        List<String> fieldsStrList = new ArrayList<>();

        for (int i = validColumnNum; i < lastColumnNum; i++) {
            String fields = row.getCell(i).getStringCellValue();
            fieldsStrList.add(fields);
        }

        return fieldsStrList;
    }


    /**
     * 导入excel辅助： 根据 cell 获取 value。不同的类型，取值方式不同。
     *
     * @param cell cell
     * @return value
     */
    private static Object getValueThrowDifferentType(Cell cell) {
        Object value = null;
        if (!Objects.isNull(cell)) {
            CellType cellType = cell.getCellTypeEnum();

            switch (cellType) {
                case NUMERIC:
                    value = cell.getNumericCellValue();
                    break;
                case STRING:
                    value = cell.getStringCellValue();
                    break;
                case BOOLEAN:
                    value = cell.getBooleanCellValue();
                    break;
                default:
                    break;
            }
        }

        return value;
    }


    /**
     * 导出excel辅助： 解析自定义注解，获取标题栏的list
     *
     * @param clazz
     * @param <T>
     * @return
     */
    private static <T> List<String> analysisAnnotation(Class<T> clazz) {
        List<String> titleList = new ArrayList<>();

        Field[] fields = clazz.getDeclaredFields();
        for (Field field : fields) {
            String title = "未定义";

            try {
                FieldName annotation = field.getAnnotation(FieldName.class);
                title = annotation.value();
            } catch (Exception e) {
                log.error("获取自定义注解失败！当前字段名为：" + field.getName());
            }

            titleList.add(title);
        }

        return titleList;
    }
}
